package persistence.database;

import java.sql.*;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.Map;
import java.util.Queue;
import java.util.TreeMap;

import core.Container;
import core.ContainerFacade;
import core.Item;
import core.ItemManager;
import core.Product;
import core.ProductFacade;
import core.ProductManager;
import core.RootStorageUnit;
import core.reports.ReportData;

import persistence.AllDAO;
import persistence.DataPersistor;

/**
 * AllDAO for database persistence
 * @author Sabrina
 *
 */
public class DBAllDAO implements AllDAO {
	private Map<Integer, Container> _containerIDMap;
	/**
	 * Creates a DBAllDAO
	 */
	public DBAllDAO()
	{
		_containerIDMap = new TreeMap<Integer,Container>();
	}
	
	/**
	 * @inheritDoc
	 */
	@Override
	public void save() throws Exception {
		//do nothing

	}

	/**
	 * @inheritDoc
	 */
	@Override
	public void load() throws Exception {
		loadContainers();
		loadProducts();
		loadItems();
		loadReportDate();
	}
	
	private void loadReportDate() throws Exception {
		Calendar cal = null;
		String query = "SELECT LastDateRan FROM Reports WHERE Name=?";

		PreparedStatement stmt = ((DBTransactionManager)DataPersistor.instance().
				getTransactionManager()).getConnection().prepareStatement(query);
		stmt.setString(1, "RemovedItemsReport");
		ResultSet rs = stmt.executeQuery();
		while(rs.next()) {
			cal = Calendar.getInstance();
			cal.setTime(rs.getDate(1));
			break;
		}
		
		if(rs != null) rs.close();
		if(stmt != null) stmt.close();
		ReportData.instance().initRemovedItemsRan(cal);
	}
	
	
	/**
	 * @throws Exception
	 */
	private void loadItems() throws Exception{
		//get all of the items from the DB
		String query = "SELECT Barcode, EntryDate, ExitDate, Product, Container FROM Items;";
		PreparedStatement stmt = ((DBTransactionManager)DataPersistor.instance().
				getTransactionManager()).getConnection().prepareStatement(query);
		ResultSet rs = stmt.executeQuery();
		
		long nextBarcode = 39999999999L;
		//for each item
		while(rs.next()){
			//create the item
			//get the item's product
			String productID = rs.getString(4);
			Product tempProduct = ProductManager.instance().getProduct(productID);
			if(tempProduct == null){
				throw new Exception("SQLite: ProductID doesn't exist");
			}
			//get the item's barcode
			String itemBarcode = rs.getString(1);
			itemBarcode = itemBarcode.substring(0, 11);
			//get the item's container
			Item tempItem = new Item(tempProduct, itemBarcode);
			//tempItem.setManager(ItemManager.instance());
			
			long currentBarcode = Long.parseLong(itemBarcode);
			if (currentBarcode > nextBarcode)
			{
				nextBarcode = currentBarcode;
			}
			
			
			//set the entry and exit dates
			tempItem.changeEntryDate(rs.getDate(2));
			if(rs.getDate(3) != null){
				Calendar tempCal = Calendar.getInstance();
				tempCal.setTime(rs.getDate(3));
				tempItem.setExitDate(tempCal);
			}
			//add it to the ItemManager
			ItemManager.instance().addItem(tempItem);
			//move it to the appropriate container
			Container newContainer = _containerIDMap.get(rs.getInt(5));
			if(newContainer == null){
				throw new Exception("SQLite: ContainerID doesn't exist");
			}
			ItemManager.instance().setContainer(tempItem, newContainer);
		}
		if(rs != null) rs.close();
		if(stmt != null) stmt.close();
		
		ItemManager.instance().setNextBarcode(++nextBarcode);
	}
	
	private void loadContainers() throws Exception{
		// Initialize map
		_containerIDMap.put(0, ContainerFacade.instance().get_rootUnit());
		
		// Initialize QUEUE
		Queue<Container> toProcess = new LinkedList<Container>();
		toProcess.add(ContainerFacade.instance().get_rootUnit());
		
		while(!toProcess.isEmpty()){
			Container current = toProcess.remove();
			String query = "SELECT ContainerID, Name, ThreeMonthValue, ThreeMonthUnit " +
					"FROM Containers WHERE ParentID=" + Integer.toString(current.getID());
			PreparedStatement stmt = ((DBTransactionManager)DataPersistor.instance().
					getTransactionManager()).getConnection().prepareStatement(query);
			
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				float supplyValue = rs.getFloat(3);
				String supplyUnit = rs.getString(4);
				
				Container added = null;
				if (current instanceof RootStorageUnit)
					added = ContainerFacade.instance().createStorageUnit(name);
				else
					added = ContainerFacade.instance().createProductGroup(
							current, name, Float.toString(supplyValue), supplyUnit);
				
				added.setID(id);
				toProcess.add(added);
				_containerIDMap.put(id, added);
			}
			if(rs != null) rs.close();
			if(stmt != null) stmt.close();
		}
	}
	
	private void loadProducts() throws Exception{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try
		{
			String sql = "Select * From Products;";
			DBTransactionManager mgr = (DBTransactionManager)(DataPersistor.instance().
					getTransactionManager());
			Connection connection = mgr.getConnection();
			stmt = connection.prepareStatement(sql);
			
			rs = stmt.executeQuery();
			while (rs.next())
			{
				String barcode = rs.getString(1);
				String description = rs.getString(2);
				float sizeValue = rs.getFloat(3);
				String sizeUnit = rs.getString(4);
				int threeMo = rs.getInt(5);
				int shelfLife = rs.getInt(6);
				Product prod = new Product (barcode, description, Float.toString(sizeValue),
						sizeUnit, shelfLife, threeMo);

				ProductFacade.instance().loadProduct(prod);
				addProductToContainers(barcode);	
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if (stmt != null)
					stmt.close();
			}
			finally
			{
				if (rs != null)
					rs.close();
			}
		}
	}

	private void addProductToContainers(String barcode) throws Exception {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try
		{
			String sql = "Select ContainerID From ProductInContainer Where ProductID = ?;";
			DBTransactionManager mgr = (DBTransactionManager)(DataPersistor.instance().
					getTransactionManager());
			Connection connection = mgr.getConnection();
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, barcode);
			rs = stmt.executeQuery();
			
			while (rs.next())
			{
				int containerID = rs.getInt(1);
				Container container = _containerIDMap.get(containerID);
				Product product = ProductManager.instance().getProduct(barcode);
				ProductFacade.instance().addProductToContainerNoSave(product, container);
			}
		} catch (Exception e) {
			throw new Exception (e.getMessage());
		}
		finally
		{
			try
			{
				if (stmt != null)
					stmt.close();
			}
			finally
			{
				if (rs != null)
					rs.close();
			}
		}
		
	}

}
